
package database;

import core.COINCOMOSubComponent;
import core.COINCOMOUnit;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 *
 * @author Raed Shomali
 */
public class COINCOMOSubComponentManager
{
    public static float get_Adaptive_Adjustment_Factor( long subComponentID , DBConnection connection )
    {
        float aaf = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_AAF(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    aaf = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return aaf;
    }

    public static float get_Adaptive_Size( long subComponentID , DBConnection connection )
    {
        float size = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_Size(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    size = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return size;
    }

    public static float get_EAF( long subComponentID , DBConnection connection )
    {
        float eaf = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_EAF(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    eaf = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return eaf;
    }

    public static float get_COST( long subComponentID , DBConnection connection )
    {
        float cost = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_Cost(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    cost = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return cost;
    }

    public static float get_INST_Cost( long subComponentID , DBConnection connection )
    {
        float cost = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_INST_COST(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    cost = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return cost;
    }

    public static float get_PROD( long subComponentID , DBConnection connection )
    {
        float prod = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_Prod(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    prod = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return prod;
    }

    public static float get_Staff( long subComponentID , DBConnection connection )
    {
        float staff = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_FSWP(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    staff = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return staff;
    }

    public static float get_Estimated_Effort( long subComponentID , DBConnection connection )
    {
        float effort = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_PMns(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    effort = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return effort;
    }

    public static float get_Nominal_Effort( long subComponentID , DBConnection connection )
    {
        float effort = 0;

        // Check if a Connection is available ..
        if ( connection != null )
        {
            try
            {
                StringBuffer sql = new StringBuffer();

                sql.append( "SELECT Get_PMnominal(" );
                sql.append( "" + subComponentID + ");" );

                // Efficient Query Statement
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                ResultSet rs = preparedStatement.executeQuery();

                // Check if a Row is returned ..
                if ( rs != null && rs.next() )
                {
                    effort = rs.getFloat( 1 );
                }

                // Release From Memory
                preparedStatement.close();
            }
            catch ( SQLException e )
            {
                // Print any Problems ...
                e.printStackTrace();
            }
        }

        return effort;
    }

    private static String insertSubComponentSQL( COINCOMOSubComponent subComponent )
    {
        StringBuffer sql = new StringBuffer();

        sql.append( "SELECT Insert_SubComponent(" );
        sql.append( "" + subComponent.getParentID() + "," );
        sql.append( "\'" + subComponent.getName() + "\'," );
        sql.append( "\'" + subComponent.getLanguage() + "\');" );

        return sql.toString();
    }

    public static COINCOMOSubComponent insertSubComponent( long componentID )
    {
        COINCOMOSubComponent subComponent = null;

        DBConnection connection = (DBConnection) COINCOMODatabaseManager.getConnection();

        // Insert Only When a Connection is available ..
        if ( connection != null )
        {
            try
            {
                subComponent = new COINCOMOSubComponent();
                subComponent.setName( "(name)" );

                int rating[] =
                {
                    3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3 , 3
                };
                int percent[] =
                {
                    0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0 , 0
                };

                subComponent.setRatings( rating );
                subComponent.setPercent( percent );
                subComponent.setParentID( componentID );

                subComponent.setDesignModified( 100 );
                subComponent.setCodeModified( 100 );
                subComponent.setIntegrationModified( 100 );

                subComponent.setSoftwareUnderstanding( 30 );
                subComponent.setAssementAssimilation( 4 );
                subComponent.setUnfamiliarity( 0.4f );
                subComponent.setAutoTranslationProductivity( 2400 );
                subComponent.setAutomaticallyTranslated( 0 );

                // SQL Insert
                String sql = insertSubComponentSQL( subComponent );

                // For Better Performance Insert Statements
                PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                // Do the insertion ...
                ResultSet rs = preparedStatement.executeQuery();

                try
                {
                    // Get Row
                    if ( rs.next() )
                    {
                        subComponent.setUnitID( rs.getInt( 1 ) );

                        // Get the EAF ...
                        subComponent.setEaf( get_EAF( subComponent.getUnitID() , connection ) );
                    }
                }
                catch ( SQLException se )
                {
                    return null;
                }
                finally
                {
                    // Release from memory ...
                    preparedStatement.close();
                }
            }
            catch ( SQLException e )
            {
                return null;
            }
            finally
            {
                // Disconnect
                COINCOMODatabaseManager.disconnect( connection );
            }
        }

        return subComponent;
    }

    public static boolean updateSubComponent( COINCOMOSubComponent subComponent )
    {
        Vector<COINCOMOUnit> vector = new Vector<COINCOMOUnit>();

        vector.add( subComponent );

        return updateSubComponent( vector );
    }
    
   
    
    public static boolean updateSubComponent( Vector<COINCOMOUnit> subComponents )
    {
        boolean isSuccessful = false;

        // If Exists
        if ( subComponents != null && !subComponents.isEmpty() )
        {
            DBConnection connection = (DBConnection) COINCOMODatabaseManager.getConnection();

            if ( connection != null )
            {
                try
                {
                    StringBuilder sql = new StringBuilder();

                    sql.append( "update sub_components_table set " +
                            "module_Name = ? , " +
                            "labor_rate = ? , " +
                            "module_size.breakage = ? , " +
                            "module_size.sizing_typ = ? , " +
                            "module_size.module_language = ? , " +
                            "module_size.sloc = ? , " +
                            "module_size.adaptation_and_reuse.initial_sloc = ? , " +
                            "module_size.adaptation_and_reuse.design_modified = ? , " +
                            "module_size.adaptation_and_reuse.code_modified = ? , " +
                            "module_size.adaptation_and_reuse.integration_modified = ? , " +
                            "module_size.adaptation_and_reuse.software_understanding = ? , " +
                            "module_size.adaptation_and_reuse.assement_and_assimilation = ? , " +
                            "module_size.adaptation_and_reuse.unfamiliarity_with_software = ? , " +
                            "module_size.adaptation_and_reuse.automatically_translated = ? , " +
                            "module_size.adaptation_and_reuse.auto_translation_productivity = ? , " +
                            "module_size.function_points.change_multiplier = ? , " +
                            "module_size.function_points.internal_logical_files.low = ? , " +
                            "module_size.function_points.internal_logical_files.average = ? , " +
                            "module_size.function_points.internal_logical_files.high = ? , " +
                            "module_size.function_points.external_interface_files.low = ? , " +
                            "module_size.function_points.external_interface_files.average = ? , " +
                            "module_size.function_points.external_interface_files.high = ? , " +
                            "module_size.function_points.external_inputs.low = ? , " +
                            "module_size.function_points.external_inputs.average = ? , " +
                            "module_size.function_points.external_inputs.high = ? , " +
                            "module_size.function_points.external_outputs.low = ? , " +
                            "module_size.function_points.external_outputs.average = ? , " +
                            "module_size.function_points.external_outputs.high = ? , " +
                            "module_size.function_points.external_inquiries.low = ? , " +
                            "module_size.function_points.external_inquiries.average = ? , " +
                            "module_size.function_points.external_inquiries.high = ? , " );

                    for ( int i = 1 ; i <= 18 ; i++ )
                    {
                        if ( i == 18 )
                        {
                            sql.append( " eaf.post_architecture.effort_multiplier_base[ " + i + " ] = ? , " +
                                    " eaf.post_architecture.effort_multiplier_inc[ " + i + " ] = ? " );
                        }
                        else
                        {
                            sql.append( " eaf.post_architecture.effort_multiplier_base[ " + i + " ] = ? , " +
                                    " eaf.post_architecture.effort_multiplier_inc[ " + i + " ] = ? , " );
                        }
                    }

                    sql.append( "where module_ID = ? " );

                    // Efficient Way of Updating
                    PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql.toString() );

                    for ( int i = 0 ; i < subComponents.size() ; i++ )
                    {
                        COINCOMOSubComponent subComponent = (COINCOMOSubComponent) subComponents.get( i );

                        // Replace "?" With Respective Values ..
                        preparedStatement.setString( 1 , subComponent.getName() );
                        preparedStatement.setFloat( 2 , subComponent.getLaborRate() );
                        preparedStatement.setFloat( 3 , subComponent.getBreakage() );
                        preparedStatement.setLong( 4 , subComponent.getSizingType() );
                        preparedStatement.setString( 5 , subComponent.getLanguage() );
                        preparedStatement.setLong( 6 , subComponent.getSLOC() );
                        preparedStatement.setFloat( 7 , subComponent.getInitialSLOC() );
                        preparedStatement.setFloat( 8 , subComponent.getDesignModified() );
                        preparedStatement.setFloat( 9 , subComponent.getCodeModified() );
                        preparedStatement.setFloat( 10 , subComponent.getIntegrationModified() );
                        preparedStatement.setFloat( 11 , subComponent.getSoftwareUnderstanding() );
                        preparedStatement.setFloat( 12 , subComponent.getAssementAssimilation() );
                        preparedStatement.setFloat( 13 , subComponent.getUnfamiliarity() );
                        preparedStatement.setFloat( 14 , subComponent.getAutomaticallyTranslated() );
                        preparedStatement.setFloat( 15 , subComponent.getAutoTranslationProductivity() );
                        preparedStatement.setLong( 16 , subComponent.getChangeMultiplier() );
                        preparedStatement.setLong( 17 , subComponent.getInternalLogicalFiles()[ 0] );
                        preparedStatement.setLong( 18 , subComponent.getInternalLogicalFiles()[ 1] );
                        preparedStatement.setLong( 19 , subComponent.getInternalLogicalFiles()[ 2] );
                        preparedStatement.setLong( 20 , subComponent.getExternalInterfaceFiles()[ 0] );
                        preparedStatement.setLong( 21 , subComponent.getExternalInterfaceFiles()[ 1] );
                        preparedStatement.setLong( 22 , subComponent.getExternalInterfaceFiles()[ 2] );
                        preparedStatement.setLong( 23 , subComponent.getExternalInputs()[ 0] );
                        preparedStatement.setLong( 24 , subComponent.getExternalInputs()[ 1] );
                        preparedStatement.setLong( 25 , subComponent.getExternalInputs()[ 2] );
                        preparedStatement.setLong( 26 , subComponent.getExternalOutputs()[ 0] );
                        preparedStatement.setLong( 27 , subComponent.getExternalOutputs()[ 1] );
                        preparedStatement.setLong( 28 , subComponent.getExternalOutputs()[ 2] );
                        preparedStatement.setLong( 29 , subComponent.getExternalInquiries()[ 0] );
                        preparedStatement.setLong( 30 , subComponent.getExternalInquiries()[ 1] );
                        preparedStatement.setLong( 31 , subComponent.getExternalInquiries()[ 2] );

                        int x = 32;

                        for ( int j = 0 ; j < 18 ; j++ )
                        {
                            preparedStatement.setInt( x++ , subComponent.getRatings()[j] );
                            preparedStatement.setInt( x++ , subComponent.getPercent()[j] );
                        }

                        preparedStatement.setLong( 68 , subComponent.getUnitID() );

                        // Update
                        preparedStatement.executeUpdate();

                        // Update Generated Fields
                        subComponent.setEaf( COINCOMOSubComponentManager.get_EAF( subComponent.getUnitID() , connection ) );
                        subComponent.setCost( COINCOMOSubComponentManager.get_COST( subComponent.getUnitID() , connection ) );
                        subComponent.setInstCost( COINCOMOSubComponentManager.get_INST_Cost( subComponent.getUnitID() , connection ) );
                        subComponent.setProd( COINCOMOSubComponentManager.get_PROD( subComponent.getUnitID() , connection ) );
                        subComponent.setStaff( COINCOMOSubComponentManager.get_Staff( subComponent.getUnitID() , connection ) );
                        subComponent.setEstEffort( COINCOMOSubComponentManager.get_Estimated_Effort( subComponent.getUnitID() , connection ) );
                        subComponent.setNomEffort( COINCOMOSubComponentManager.get_Nominal_Effort( subComponent.getUnitID() , connection ) );
                        subComponent.setAdaptiveSLOC( COINCOMOSubComponentManager.get_Adaptive_Size( subComponent.getUnitID() , connection ) );
                        subComponent.setAdaptationAdjustmentFactor( COINCOMOSubComponentManager.get_Adaptive_Adjustment_Factor( subComponent.getUnitID() , connection ) );
                    }

                    // Free From Memory
                    preparedStatement.close();

                    // Update Status
                    isSuccessful = true;
                }
                catch ( SQLException e )
                {
                    // Print Any Problems ..
                    e.printStackTrace();
                }
                finally
                {
                    // Disconnect
                    COINCOMODatabaseManager.disconnect( connection );
                }
            }
        }

        return isSuccessful;
    }

    public static boolean deleteSubComponents( Vector<COINCOMOSubComponent> SubComponents )
    {
        boolean isSuccessful = false;

        // If Exists
        if ( SubComponents != null && !SubComponents.isEmpty() )
        {
            DBConnection connection = (DBConnection) COINCOMODatabaseManager.getConnection();

            // Check if connection exists
            if ( connection != null )
            {
                try
                {
                    String sql = "delete from sub_components_table where module_ID=?";

                    // Efficent Deletion
                    PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement( sql );

                    for ( int i = 0 ; i < SubComponents.size() ; i++ )
                    {
                        COINCOMOSubComponent SubComponent = SubComponents.get( i );

                        // Replace "?" With ID Value
                        preparedStatement.setLong( 1 , SubComponent.getUnitID() );

                        // Delete
                        preparedStatement.executeUpdate();
                    }

                    // Release From Memory
                    preparedStatement.close();

                    // Update Status
                    isSuccessful = true;
                }
                catch ( SQLException e )
                {
                    // Print any Problems ...
                    e.printStackTrace();
                }
                finally
                {
                    // Disconnect
                    COINCOMODatabaseManager.disconnect( connection );
                }
            }
        }

        return isSuccessful;
    }

}
